package com.diapoclic.entitydao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.diapoclic.entitybean.Connect;
import com.diapoclic.entitybean.Document;
import com.diapoclic.entitybean.Folder;

public class FolderDao {

	

	private UserDao userdao=new UserDao();
	private DocumentDao documentdao=new DocumentDao();

	private Connect connectiondb = new Connect();
	private Connection con = connectiondb.connect();	
	private SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");


	private Folder createFolder (ResultSet res) throws SQLException
	{
		Folder newFolder = new Folder();
		newFolder.setIdFolder(res.getInt(1));
		newFolder.setNameFolder(res.getString(2));
		newFolder.setNbDoc(res.getInt(3));
		newFolder.setDateCreation(res.getDate(4));
		newFolder.setDateUpdate(res.getDate(5));
		newFolder.setUser(userdao.findById((res.getInt(6))));
		return newFolder;
		
	}

	public Connection getCon ()
	{
		return this.con;
	}
	
	public Connect getConnexiondb()
	{
		return this.connectiondb;
	}
	
	public Folder findById(int id) throws SQLException
	{
		Folder newFolder = new Folder();
		String query;
		Statement state = con.createStatement();
		query="SELECT * FROM Folder WHERE id_folder="+id;
		ResultSet result = state.executeQuery(query);
		
		if (result.next()) {
			newFolder=createFolder(result);

		 }
		return new Folder();

	}
	
	public List<Folder> findByIds(int[] ids) throws SQLException
	{
		List<Folder> tabFolder=new ArrayList<Folder>();
		String query;

		
		Statement state = con.createStatement();
		for(int i=0;i<ids.length;i++)
		{
		query="SELECT * FROM Folder WHERE id_folder="+ids[i];
		ResultSet result = state.executeQuery(query);
		if (result.next()) {
			 tabFolder.add(createFolder(result));
			 
		 }
		
		}
		return tabFolder ;
	}
	
	public List<Folder> findByIdUser(int id) throws SQLException
	{
		List<Folder> tabFolder=new ArrayList<Folder>();
		String query;

		
		Statement state = con.createStatement();
		query="SELECT * FROM Folder WHERE id_user="+id;
		ResultSet result = state.executeQuery(query);
		while (result.next()) {
			 tabFolder.add(createFolder(result));
		 }
		
		
		return tabFolder ;
	}
	
	
	public int addFolder (String newName, int newNb, int newIdUser) throws SQLException
	{
		Date today=new Date();
		String query;
		Statement state = con.createStatement();
		query="INSERT INTO `diapoclic`.`Folder` (`id_folder`, `name_folder`, `nb_doc`, `date_creation`, `date_update`, `id_user`) "; 
		query+="VALUES (NULL, '" +newName+ "', '" +newNb+ "', '" +sdf.format(today)+ "', '" +sdf.format(today)+ "', '" +newIdUser+ "');";
		
		
		int result = state.executeUpdate(query);
		return result;
	}
	
	public int updateFolder(Folder folderUpdate) throws SQLException
	{
		Date today=new Date();
		String query;
		Statement state = con.createStatement();
		query="UPDATE `diapoclic`.`Folder` SET";
		query+="`name_folder` = '" +folderUpdate.getNameFolder()+ "',";
		query+="`nb_doc` = '" +folderUpdate.getNbDoc()+ "',";
		query+="`date_update` = '" +sdf.format(today)+ "'";
		query+="WHERE `Folder`.`id_folder` ="+folderUpdate.getIdFolder()+" ;";
		
		int result = state.executeUpdate(query);

		return result;
		
	}
	
	
	public List<Document> listDocumentByFolder(int idFolder) throws SQLException
	{
		List<Document> tabDoc=new ArrayList<Document>();
		String query;
		Statement state = con.createStatement();
		query="SELECT * FROM content WHERE id_folder="+idFolder;
		ResultSet result = state.executeQuery(query);
		
		
		while (result.next()) {
			tabDoc.add( documentdao.findById(result.getInt(2)));
		 }

		return tabDoc ;
		
	}
	
	public int addDocumentInFolder (int idDocument,int idFolder) throws SQLException
	{
		String query;
		Statement state = con.createStatement();
		query="INSERT INTO `diapoclic`.`content` (`id_folder`, `id_doc`) "; 
		query+="VALUES ('" +idFolder+ "', '" +idDocument+ "');";
		
		FolderDao folderDao =new FolderDao();
		folderDao.findById(idFolder).incrementNbDoc();
		
		int result = state.executeUpdate(query);
		return result;
	}

	
	public int removeDocument (int idFolder,int idDocument) throws SQLException
	{
		String query;
		Statement state = con.createStatement();
		query="DELETE FROM `diapoclic`.`content` WHERE `content`.`id_folder` =" +idFolder+ " AND `content`.`id_doc`=" +idDocument+ ";"; 
		
		int result = state.executeUpdate(query);
		con.close();

		return result;
	}
	
	public int remove (int idFolder) throws SQLException
	{
		String query;
		Statement state = con.createStatement();
		List<Document> tabDoc=new ArrayList<Document>();

		
		query="SELECT * FROM content WHERE id_folder="+idFolder;
		ResultSet result = state.executeQuery(query);
		
		
		while (result.next()) {
			//liste des id des documents contenu dans le folder
			tabDoc.add( documentdao.findById(result.getInt(2)) );
		 }
		//suppression des document de contenu dans le Folder
		for (int i=0;i<tabDoc.size();i++)
		{
			int res = this.removeDocument(idFolder, tabDoc.get(i).getIdDoc());
		}
		
		//Suppression du Folder
		query="DELETE FROM `diapoclic`.`Folder` WHERE `Folder`.`id_folder` =" +idFolder + ";"; 
		int result2 = state.executeUpdate(query);

		System.out.println("Id Folder : " + idFolder + "résultat de suppression : "+result2);

		con.close();

		return result2;
	}
	
	
	
}
